use std::sync::{Arc, Mutex};

use lazy_static::lazy_static;
use log::{info, trace};
use sea_orm::prelude::Expr;
use sea_orm::sea_query::{Query, SqliteQueryBuilder};
use sea_orm::{ConnectionTrait, Database, DatabaseConnection, DbBackend, QueryResult, Statement};

use tokio::runtime::Runtime;

use anyhow::anyhow;
use anyhow::{Ok, Result};

pub static CONNECTION: Mutex<Option<DatabaseConnection>> = Mutex::new(None);
pub static INDEX: std::sync::LazyLock<Arc<Mutex<i64>>> = std::sync::LazyLock::new(|| Arc::new(Mutex::new(0)));

lazy_static! { // You can also use `once_cell`
    pub static ref RT: Runtime = Runtime::new().unwrap();
}

pub struct DbColumnDefine<'a> {
    pub col_name: &'a str,
    pub col_type: &'a str,
    pub sql_alter: &'a str,
}

impl<'a> DbColumnDefine<'a> {
    pub fn new(col_name: &'a str, col_type: &'a str, sql_alter: &'a str) -> Self {
        DbColumnDefine { col_name, col_type, sql_alter }
    }
}

pub fn init() -> Result<()> {
    info!("Initializing database");

    init_connection()
}

pub fn init_connection() -> Result<()> {
    let mut con = CONNECTION.lock().map_err(|e| anyhow!("Error locking connection: {}", e))?;

    let connection = std::thread::spawn(move || {
        RT.handle()
            .block_on(async move { Database::connect("sqlite://./mydatabase.db?mode=rwc").await.unwrap() })
    })
    .join()
    .map_err(|e| anyhow!("Error joining thread: {:?}", e))?;

    info!("Database connection established");
    con.replace(connection);

    Ok(())
}

pub async fn init_tables(table_name: &str, create_query: &str, table_columns: &[DbColumnDefine<'_>]) -> Result<()> {
    let connection = get_connection()?;

    let table_exists: bool = connection
        .query_one(Statement::from_string(
            DbBackend::Sqlite,
            Query::select()
                .expr(Expr::val(1))
                .from("sqlite_master")
                .and_where(Expr::col("type").eq("table"))
                .and_where(Expr::col("name").eq(table_name))
                .to_string(SqliteQueryBuilder),
        ))
        .await
        .map(|row| match row {
            Some(result) => {
                if result.column_names().contains(&"1".to_string()) {
                    true
                } else {
                    false
                }
            }
            None => false,
        })
        .unwrap_or(false);

    if !table_exists {
        connection.execute_unprepared(create_query).await?;
    }

    // 检查字段是否存在
    let result = connection
        .query_all(Statement::from_string(DbBackend::Sqlite, format!("PRAGMA table_info({})", table_name)))
        .await?;

    for column in table_columns.iter() {
        if !check_column_exists(column.col_name, &result) {
            info!("Column {} does not exist, creating it", column.col_name);
            connection.execute(Statement::from_string(DbBackend::Sqlite, column.sql_alter)).await?;
        }
    }
    Ok(())
}

pub fn get_connection() -> Result<sea_orm::DatabaseConnection> {
    let connection = CONNECTION
        .lock()
        .map_err(|e| anyhow!("Error locking connection: {}", e))?
        .clone()
        .ok_or_else(|| anyhow!("No connection available"))?;
    Ok(connection)
}

pub fn get_index() -> Result<i64> {
    let mut index = INDEX.lock().map_err(|e| anyhow!("Error locking index: {}", e))?;
    *index += 1;
    Ok(*index)
}

pub fn check_column_exists(column_name: &str, result: &Vec<QueryResult>) -> bool {
    let has_column = result.iter().any(|row| {
        let col_cid = row.try_get::<String>("", "cid").unwrap_or_default();
        let col_name = row.try_get::<String>("", "name").unwrap_or_default();
        let col_type = row.try_get::<String>("", "type").unwrap_or_default();
        let col_notnull = row.try_get::<i32>("", "notnull").unwrap_or_default();
        let col_default = row.try_get::<String>("", "dflt_value").unwrap_or_default();
        let col_pk = row.try_get::<i32>("", "pk").unwrap_or_default();

        trace!(
            "cid: {} name: {} type: {} notnull: {} default: {} pk: {}",
            col_cid, col_name, col_type, col_notnull, col_default, col_pk
        );

        col_name == column_name
    });
    has_column
}
